/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.bios.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class StudentDAO {

    private static final String GET_STUDENT = "SELECT * from student where userID= ?";
    private static final String UPDATE_EDOLLARS = "Update student set eDollar = ? where userID= ?";
    private static final String GET_ALL = "SELECT * from student";
    private static final String INSERT = "INSERT into student values (?,?,?,?,?)";
    private static final String DELETE = "DELETE from student";
    private static final String VALIDATE_STUDENT = "SELECT * from student where userID= ? and password = ?";

    
    /**
    *This method add one student into database
    *
    *@param the student object needed to be added into the database 
    */
    public static void add(Student student) {
        
        //init the connection, the prepared statement
        Connection conn = null;
        PreparedStatement pst = null;

        //prepare the sql statement and execute it 
        try {
            conn = ConnectionManager.getConnection();
            pst = conn.prepareStatement(INSERT);
            pst.setString(1, student.getUserID());
            pst.setString(2, student.getPassword());
            pst.setString(3, student.getName());
            pst.setString(4, student.getSchool());
            pst.setDouble(5, student.geteDollars());
            pst.executeUpdate();
        
        //catch the exception thrown by executing the statement
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnectionManager.close(conn, pst);
        }
    }


    /**
    *This method removes all students from database
    */
    public static void removeAll() {
        
        //init the connection, the prepared statement
        Connection conn = null;
        PreparedStatement pst = null;
        
        //execute the sql statement
        try {
            conn = ConnectionManager.getConnection();
            pst = conn.prepareStatement(DELETE);
            // rs = pst.executeQuery();
            pst.executeUpdate();

        //catch the exception thrown by executing the statement
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnectionManager.close(conn, pst);
        }
    }


    /**
    *This method modifies the edollar of one student
    *
    *@param the user id of student and the new amount
    */
    public static void updateEDollars(String userID, double amount) {
        
        //init the connection, the prepared statement and the result set
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;

        //prepare the sql statement and execute it 
        try {
            conn = ConnectionManager.getConnection();
            pst = conn.prepareStatement(UPDATE_EDOLLARS);
            pst.setDouble(1, amount);
            pst.setString(2, userID);
            pst.executeUpdate();
        
        //catch the exception thrown by executing the statement
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnectionManager.close(conn, pst, rs);
        }
    }


    /**
    *This method retrieve all students stored in database
    *
    *@return an array list of student objects 
    */
    public static ArrayList<Student> retrieveAll() {
        
        //init the value to return, the connection, the prepared statement and the result set
        ArrayList<Student> students = new ArrayList<Student>();
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;

        //execute the sql statement
        try {
            conn = ConnectionManager.getConnection();
            pst = conn.prepareStatement(GET_ALL);
            rs = pst.executeQuery();
            while (rs.next()) {
                students.add(new Student(rs.getString("userID"), rs.getString("Password"), rs.getString("Name"), rs.getString("School"), rs.getDouble("eDollar")));
            }
        
        //catch the exception thrown by executing the statement
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnectionManager.close(conn, pst, rs);
        }

        return students;
    }

    /**
     * This method retrieves one student from the database
     *
     * @param id the student
     * @return the specified Student object
     */
    public static Student retrieve(String userID) {

        Student student = null;
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;

        try {
            conn = ConnectionManager.getConnection();
            pst = conn.prepareStatement(GET_STUDENT);
            pst.setString(1, userID);
            rs = pst.executeQuery();
            if (rs.next()) {
                student = new Student(rs.getString("userID"), rs.getString("password"), rs.getString("Name"), rs.getString("School"), rs.getDouble("eDollar"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnectionManager.close(conn, pst, rs);
        }
        return student;
    }

    
    /**
    *This method validate a student
    *
    *@param user id and password of the student
    *@return student object
    */
    public static Student validateStudent(String userID, String password){
        
        //init the value to return, the connection, the prepared statement and the result set
        Student student = null;
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;

        //prepare the sql statement and execute it
        try {
            conn = ConnectionManager.getConnection();
            pst = conn.prepareStatement(VALIDATE_STUDENT);
            pst.setString(1, userID);
            pst.setString(2, password);
            rs = pst.executeQuery();
            if (rs.next()) {
                student = new Student(rs.getString("userID"), rs.getString("Password"), rs.getString("Name"), rs.getString("School"), rs.getDouble("eDollar"));
            }
             
        //catch the exception thrown by executing the statement
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnectionManager.close(conn, pst, rs);
        }

        return student;
    }
}
